﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SGArchiveSystem7xDB.Linq
{
    public class T_ArchiveDal
    {
        static SGArchiveSystem7xEntities conn;
        public static T_ArchiveAj Get_SingleArchive(string dh)
        {
            try
            {
                conn = new SGArchiveSystem7xEntities();
                return conn.T_ArchiveAj.Select(c => c).Where(c => c.DH == dh).Single();
            }
            catch { return null; }
        }
        public static T_ArchiveAj Get_SingleArchive(int id)
        {
            try
            {
                conn = new SGArchiveSystem7xEntities();
                return conn.T_ArchiveAj.Select(c => c).Where(c => c.ID == id).Single();
            }
            catch { return null; }
        }
        /// <summary>
        /// 判断档号是否重复
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="DH"></param>
        /// <returns></returns>
        public static int Get_ISDH(int ID, string DH)
        {
            try
            {
                int value = -2;
                conn = new SGArchiveSystem7xEntities();
                if (ID >-1)
                {
                    value = conn.T_ArchiveAj.Select(c => c).Where(c => c.DH == DH && c.ID != ID).Count();
                }
                else//添加时
                {
                    value = conn.T_ArchiveAj.Select(c => c).Where(c => c.DH == DH).Count();
                }
                return value;

            }
            catch { return -1; }
        }
        public static List<T_ArchiveAj> GetArchive(int ajzt, out int count, int start = -1, int row = -1)
        {
            count = 0;
            try
            {
                conn = new SGArchiveSystem7xEntities();
                var query = conn.T_ArchiveAj.Select(c => c);
                if (ajzt > -1)
                {
                    query = query.Where(c => c.AJZT == 0);
                }
                if (start != -1 && row != -1)
                {
                    count = query.Count();
                    return query.OrderBy(c => c.ID).Skip(start).Take(row).ToList();
                }
                else
                {
                    var list = query.ToList();
                    count = list.Count;
                    return list;
                }
            }
            catch
            {
                return null;
            }
        }
        /// <summary>
        /// 查询数据库
        /// </summary>
        /// <param name="where"></param>
        /// <param name="count"></param>
        /// <param name="start"></param>
        /// <param name="row"></param>
        /// <returns></returns>
        public static List<T_ArchiveAj> Query_Archive(string where, out int count, int start = -1, int row = -1)
        {
            count = 0;
            conn = new SGArchiveSystem7xEntities();
            string sql = "select * from  T_ArchiveAj";// where ="+where;
            if (where != null && where != "")
                sql = sql + " where  " + where;
            var query = conn.Database.SqlQuery<T_ArchiveAj>(sql);
            if (start != -1 && row != -1)
            {
                count = query.Count();
                return query.OrderBy(c => c.ID).Skip(start).Take(row).ToList();
            }
            else
            {
                var list = query.ToList();
                count = list.Count;
                return list;
            }
        }
        public static DataTable Query_Archive_xls(string where)
        {
            SGArchiveSystem7xEntities database = new SGArchiveSystem7xEntities();
            string sql = "select * from  T_ArchiveAj";// where ="+where;
            if (where != null && where != "")
                sql = sql + " where  " + where;
            SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            try
            {
                conn.ConnectionString = database.Database.Connection.ConnectionString;
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }

                SqlCommand cmd = new SqlCommand(sql,conn);
                DataSet ds = new DataSet();
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);

                conn.Close();//连接需要关闭  
                conn.Dispose();
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                conn.Close();
                conn.Dispose();
                return null;
            }

        }
        /// <summary>
        /// 操作数据（添加、修改和删除）
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="type"></param>
        /// <param name="user"></param>
        /// <param name="dh">档号</param>
        /// <param name="aid">AID为档案ID</param>
        /// <param name="arid">ARID为档案类型字段</param>
        /// <returns></returns>
        public static int Op_Archive(string sql, string type, V_User user, string dh, int aid, int arid)
        {
            SGArchiveSystem7xEntities database = new SGArchiveSystem7xEntities();

            SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            try
            {
                conn.ConnectionString = database.Database.Connection.ConnectionString;
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                int id = aid;
                if (type == "著录")
                    sql = sql + " SELECT  @@IDENTITY ";
                SqlCommand command = new SqlCommand(sql, conn);
                int value = 0;
                if (type == "著录")
                { id = Convert.ToInt32(command.ExecuteScalar());
                    value = id;
                }
                else
                    value= command.ExecuteNonQuery();
                T_LogArchiveInit en = new T_LogArchiveInit();
                en.LOG_TYPE = type;
                en.LOG_UNAME = user.U_RNAME;
                en.LOG_DATE = DateTime.Now;
                en.LOG_UID = user.U_ID;
                en.LOG_ARID = arid;
                en.LOG_ANAME = dh;
                en.LOG_AID = id;
                T_LogArchiveInitDal.Add_Log(en);
                string SQL = "";
                //if (type == "著录")
                //{
                //    SQL = "update T_ArchiveAj set SJJLR=" + user.U_NAME + ",SJJLRQ=" + DateTime.Now.ToString() + ",SJJLLX='著录' where DH=" + dh;
                //    command = new SqlCommand(SQL, conn);
                //    command.ExecuteNonQuery();
                //}else
                if (type == "修改")
                {
                    SQL = "update T_ArchiveAj set SJXGR='" + user.U_NAME + "',SJXGRQ='" + DateTime.Now.ToString() + "' where ID=" + aid;
                    command = new SqlCommand(SQL, conn);
                    command.ExecuteNonQuery();
                }
                return value;
            }
            catch (Exception ex)
            {
                return -1;
            }
            finally
            {
                conn.Close();
            }
        }

     

        /// <summary>
        /// 发布档案
        /// </summary>
        /// <param name="dh">档号</param>
        /// <param name="user">用户</param>
        /// <returns></returns>
        public static int Init(string[] dh, List<T_ArchiveAj> en,V_User user)
        {
            try
            {
                if (dh == null)
                {
                    foreach (var item in en)
                    {
                        conn = new SGArchiveSystem7xEntities();
                        string DH = item.DH;
                        var query = conn.T_ArchiveAj.Select(c => c).Where(c => c.DH == DH);
                        foreach (var nn in query)
                        {
                            nn.AJZT = 1;
                            nn.V03 = user.U_NAME;
                            nn.D01 = DateTime.Now;
                        }
                        conn.SaveChanges();
                    }
                }
                else
                {
                    for (int i = 0; i < dh.Length; i++)
                    {
                        conn = new SGArchiveSystem7xEntities();
                        string DH = dh[i];
                        var query = conn.T_ArchiveAj.Select(c => c).Where(c => c.DH == DH);
                        foreach (var nn in query)
                        {
                            nn.AJZT = 1;
                            nn.V03 = user.U_NAME;
                            nn.D01 = DateTime.Now;
                        }
                        conn.SaveChanges();
                    }
                }
                return 1;
            }
            catch
            {
                return -1;
            }
        }
        /// <summary>
        /// 临时发布档案
        /// </summary>
        /// <param name="dh">档号</param>
        /// <param name="user">用户</param>
        /// <returns></returns>
        public static int LSInit(string[] dh, List<T_ArchiveAj> en, V_User user)
        {
            try
            {
                if (dh == null)
                {
                    foreach (var item in en)
                    {
                        conn = new SGArchiveSystem7xEntities();
                        string DH = item.DH;
                        var query = conn.T_ArchiveAj.Select(c => c).Where(c => c.DH == DH);
                        foreach (var nn in query)
                        {
                            nn.I05 = 1;//I05是否为临时发布
                            nn.V03 = user.U_NAME;
                            nn.D01 = DateTime.Now;
                        }
                        conn.SaveChanges();
                    }
                }
                else
                {
                    for (int i = 0; i < dh.Length; i++)
                    {
                        conn = new SGArchiveSystem7xEntities();
                        string DH = dh[i];
                        var query = conn.T_ArchiveAj.Select(c => c).Where(c => c.DH == DH);
                        foreach (var nn in query)
                        {
                            nn.I05 = 1;
                            nn.V03 = user.U_NAME;
                            nn.D01 = DateTime.Now;
                        }
                        conn.SaveChanges();
                    }
                }
                return 1;
            }
            catch
            {
                return -1;
            }
        }
        /// <summary>
        /// 上传方法
        /// </summary>
        /// <param name="id">T_ArchiveAj.ID</param>
        /// <param name="pdfname"></param>
        /// <param name="pdfsize"></param>
        /// <param name="user"></param>
        /// <returns></returns>
        public static int Upload(string DH, string pdfname, int pdfsize, V_User user)
        {
            try
            {
                conn = new SGArchiveSystem7xEntities();
                var query = conn.T_ArchiveAj.Select(c => c).Where(c => c.DH == DH);
                foreach (var nn in query)
                {
                    nn.DZWJ = pdfname;
                    nn.V04 = user.U_NAME;
                    nn.D01 = DateTime.Now;
                    nn.I01 = pdfsize;
                    nn.I02 = 0;
                }
                conn.SaveChanges();
                return 1;
            }
            catch
            {
                return -1;
            }
        }
    }
}
